﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
namespace DAL
{
    public class SqlHelper
    {
        public static string conStr = "Data Source=SC-202010221500;Initial Catalog=ExpressDB;Integrated Security=True";
        private static SqlConnection sqlConnection = null;
        public SqlHelper()
        {
            if (SqlHelper.sqlConnection == null)
            {
                sqlConnection = Getconnection();
            }
            if (sqlConnection != null && sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }


        }
        private SqlConnection Getconnection()
        {
            //SqlConnection conn =  new SqlConnection(conStr);
            return new SqlConnection(conStr);
        }
        private void Open()
        {
            if(sqlConnection!=null&& sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
        }
        public void Close()
        {
            if (sqlConnection != null && sqlConnection.State == ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }
        //add delete update query
        public int Exec(string sql)
        {
            SqlCommand  command = new SqlCommand(sql, SqlHelper.sqlConnection);
            int result = command.ExecuteNonQuery();
            return result;
        }
        public DataTable Query(string sql)
        {
            //SqlCommand command = new SqlCommand(sql, con);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, conStr);
            DataSet ds = new DataSet();
            sqlDataAdapter.Fill(ds);
            if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
            return null;
        }
        public int Update()
        {
            return -1;
        }
    }
}
